package com.ef.dao.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;

import com.ef.constant.ELeadsEventType;
import com.ef.dao.CorpContractDao;
import com.ef.domain.CorpContract;
import com.ef.web.domain.CCPerformance;
import com.ef.web.domain.RevenueCount;

public class CorpContractDaoImpl extends DaoImpl<CorpContract> implements
		CorpContractDao {

	/**
	 * Revenue By CC
	 */
	@SuppressWarnings("unchecked")
	public List<RevenueCount> sumByCC(Date start, Date end) {
		SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd");
		SimpleDateFormat formater2 = new SimpleDateFormat("yyyy-MM-dd");
		String _start = formater.format(start);
		int _start_year = Integer.valueOf(_start.substring(0, 4));
		String _end = formater.format(end);
		int _end_year = Integer.valueOf(_end.substring(0, 4));
		Date lastStart = null;
		Date lastEnd = null;
		try {
			lastStart = formater.parse((_start_year - 1) + _start.substring(4));
			lastEnd = formater.parse((_end_year - 1) + _end.substring(4));
		} catch (ParseException e) {

			e.printStackTrace();
		}
		String strStart = formater2.format(start);
		String strEnd = formater2.format(end);
		String strLastStart = formater2.format(lastStart);
		String strLastEnd = formater2.format(lastEnd);
		List<RevenueCount> result = new ArrayList<RevenueCount>();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT A.name ,A.r1, B.r2 FROM ");
		sql.append(" (SELECT ").append(" u.name, SUM(paysum-refundsum) AS r1 ");
		sql
				.append(" FROM ( SELECT contract_id  AS contractid , SUM(DECODE(h.paytype,'Pay',h.pay,0)) AS paysum ,");
		sql
				.append(" SUM(DECODE(h.paytype,'Refund',h.pay,0)) AS refundsum  FROM ef_payhistory    h ");
		sql.append(" WHERE h.paytime BETWEEN TO_DATE('").append(strStart)
				.append("','yyyy-MM-dd')");
		sql.append(" AND TO_DATE('").append(strEnd).append("','yyyy-MM-dd') ");
		sql.append(" GROUP BY h.contract_id )    p ,");
		sql.append(" ef_contract c ,ef_leads l, ef_user u WHERE ");
		sql.append(" p.contractid = c.id ");
		sql.append(" AND c.leads_id = l.id ");
		sql.append(" AND l.cc_id = u.id ");
		sql.append(" GROUP BY u.name ) A , ");
		sql.append(" (SELECT ").append(" u.name, SUM(paysum-refundsum) AS r2 ");
		sql
				.append(" FROM ( SELECT contract_id  AS contractid , SUM(DECODE(h.paytype,'Pay',h.pay,0)) AS paysum ,");
		sql
				.append(" SUM(DECODE(h.paytype,'Refund',h.pay,0)) AS refundsum  FROM ef_payhistory    h ");
		sql.append(" WHERE h.paytime BETWEEN TO_DATE('").append(strLastStart)
				.append("','yyyy-MM-dd')");
		sql.append(" AND TO_DATE('").append(strLastEnd).append(
				"','yyyy-MM-dd') ");
		sql.append(" GROUP BY h.contract_id )    p ,");
		sql.append(" ef_contract c ,ef_leads l, ef_user u WHERE ");
		sql.append(" p.contractid = c.id ");
		sql.append(" AND c.leads_id = l.id ");
		sql.append(" AND l.cc_id = u.id ");
		sql.append(" GROUP BY u.name ) B   ");
		sql.append(" WHERE A.name = B.name(+) ");
		Query query = this.getSession().createSQLQuery(sql.toString());

		List<Object> list = query.list();
		for (int i = 0; i < list.size(); i++) {
			Object[] s = (Object[]) list.get(i);
			RevenueCount revenue = new RevenueCount();
			revenue.setTitle(s[0].toString());
			if (s[1] != null) {
				revenue.setCurrentSum(Double.valueOf(s[1].toString()));
			} else {
				revenue.setCurrentSum(0.0);
			}
			if (s[2] != null)
				revenue.setLastSum(Double.valueOf(s[2].toString()));
			else
				revenue.setLastSum(0.0);
			result.add(revenue);
		}

		return result;
	}

	public List<CCPerformance> performanceByCC(String month) {
		// SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM");
		String strMonth = month;
		List<CCPerformance> result = new ArrayList<CCPerformance>();

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT A.name,A.r1,B.r2,C.r3 FROM ");
		sql.append(" (SELECT ").append(" u.name, SUM(paysum-refundsum) AS r1 ");
		sql
				.append(" FROM ( SELECT contract_id  AS contractid , SUM(DECODE(h.paytype,'Pay',h.pay,0)) AS paysum ,");
		sql
				.append(" SUM(DECODE(h.paytype,'Refund',h.pay,0)) AS refundsum  FROM ef_payhistory    h ");
		sql.append(" WHERE TO_CHAR(h.paytime,'yyyy-MM')='");
		sql.append(strMonth).append("' ");
		sql.append(" GROUP BY h.contract_id )    p ,");
		sql.append(" ef_contract c ,ef_leads l, ef_user u WHERE ");
		sql.append(" p.contractid = c.id ");
		sql.append(" AND c.leads_id = l.id ");
		sql.append(" AND l.cc_id = u.id ");
		sql.append(" GROUP BY u.name ) A , ");
		sql
				.append(" (SELECT u.name,COUNT(*) AS r2  FROM EF_LEADSEVENTHISTORY el,ef_leads l, ef_user u ");
		sql.append(" WHERE el.leads_id = l.id ");
		sql.append(" AND l.cc_id = u.id ");
		sql.append(" AND el.EVENTTYPE = '");
		sql.append(ELeadsEventType.CCCall).append("' ");
		sql.append(" GROUP BY u.name ) B,");
		sql
				.append(" (SELECT u.name, COUNT(*) AS r3  FROM ef_contract c,ef_leads l,ef_user u ");
		sql.append(" WHERE c.leads_id = l.id ");
		sql.append(" AND l.cc_id = u.id ");
		sql.append(" GROUP BY u.name ) C ");
		sql.append(" WHERE A.name = B.name(+) ");
		sql.append(" AND A.name = C.name(+) ");

		Query query = this.getSession().createSQLQuery(sql.toString());
		List list = query.list();
		for (int i = 0; i < list.size(); i++) {
			Object[] s = (Object[]) list.get(i);
			CCPerformance ccp = new CCPerformance();
			ccp.setTitle(s[0].toString());
			if (s[1] != null)
				ccp.setRevenue(Double.valueOf(s[1].toString()));
			if (s[2] != null)
				ccp.setCall(Integer.valueOf(s[2].toString()));
			if (s[3] != null) {
				ccp.setContract(Integer.valueOf(s[3].toString()));
			}
			if (ccp.getCall() == 0) {
				ccp.setRate(0.0);
			} else {
				ccp
						.setRate(Double.valueOf(ccp.getContract()
								/ ccp.getCall()) * 100);
			}

			result.add(ccp);
		}

		return result;
	}
}
